const mysql2 = require('mysql2');
const Joi = require('@hapi/joi');
const dateFormat = require('dateformat');
const CryptoJS = require("crypto-js");

const appKey = "28127664";
const signSecret = " 4c7d682c552b9dd06ca948f68902f61f";
const password = CryptoJS.enc.Base64.stringify(CryptoJS.HmacSHA256(signSecret, signSecret));
// 云函数mysql数据库链接 
let poolObj = mysql2.createConnection({
    host: 'dataproxy-internal.m.mappcloud.com',
    port: 3306,
    user: appKey,
    password: password,
    database: 'test_' + appKey,
    waitForConnections: true,
    connectionLimit: 200,
    pool: {
        idle: 50,  //链接控制时间
    },
    timezone: '+08:00', //东八区
});


//  测试
exports.test1 = async (context) => {
    const conn = poolObj.promise();
    // sql 数据插入
    let sql = `INSERT INTO sharedetail (rule_name,description,share_number,state,start_date,end_date,gmt_create) VALUES ('adsad','asdsdd','dsasd','sasdad','sada',1,22)`;
    let data = await conn.query(sql);
    return { success: true, code: 0, data: data };
}


/**
 *  添加一条数据
 * @param {string} param.rule_name 规则名称
 * @param {string} param.description 规则描述
 * @param {number} param.share_number 分享人数
 * @param {number} param.state 被分享人的会员状态，，0，非会员,1,老会员，2，新会员
 * @param {string} param.start_date 开始时间
 * @param {string} param.end_date 结束时间
 */
exports.createOne = async (param) => {
    try {
        console.log(param,'================================')
        const conn = poolObj.promise();
        // sql 数据插入

        let sql = `INSERT INTO share_rule (rule_name,description,share_number,state,start_date,end_date,gmt_create) 
        VALUES('${param.rule_name}','${param.description}',${param.share_number},${param.state},'${param.start_date}','${param.end_date}',NOW())`;
        let data = await conn.query(sql);
        console.log(data, '7777777777777777777777777777777777');
        return data;
    } catch (e) {
        console.log(e);
    }
}


/**
 *  查询规则是否有效
 * @param {number} param.id 规则id
 * @param {string} param.start_date 开始时间
 * @param {string} param.end_date 结束时间
 */
exports.checkTimeInfo = async (param) => {
    try {
        const conn = poolObj.promise();
        let sql = `select sum(count) count from (
				SELECT
					COUNT(1) count
				FROM
					share_rule
				WHERE
					(
						'${param.start_date}' BETWEEN start_date
						AND end_date 
						OR '${param.end_date}' BETWEEN start_date
						AND end_date
					)
				UNION ALL
				SELECT
					COUNT(1) count
				FROM
					share_rule
				WHERE
					 start_date >= '${param.start_date}'
						AND end_date <= '${param.end_date}'
				
				) as a`
        if (param.id) {
            sql = `select sum(count) count from (
                SELECT
                    COUNT(1) count
                FROM
                    share_rule
                WHERE
                    (
                        '${param.start_date}' BETWEEN start_date
                        AND end_date 
                        OR '${param.end_date}' BETWEEN start_date
                        AND end_date
                    )
                AND id != ${param.id}
                UNION ALL
                SELECT
                    COUNT(1) count
                FROM
                    share_rule
                WHERE
                        start_date >= '${params.start_date}'
                        AND end_date <= '${params.end_date}'
                AND id != ${param.id}
                ) as a`;
        }
        let data = await conn.query(sql);
        return data[0][0].count;
    }
    catch (e) {
        console.log(e);
    }
}


/**
 *  根据id查询数据
 * @param {string} param.id 规则id
 */
exports.findAllById = async (param) => {
    try {
        const conn = poolObj.promise();

        let sql = `SELECT * FROM share_rule WHERE id=${param.id}`;
        let data = await conn.query(sql);
        console.log(data, '7777777777777777777777777777777777');
        return data;
    }
    catch (e) {
        console.log(e);
    }

}

/**
 *  根据id删除数据
 * @param {string} param.id 规则id
 */
exports.deleteById = async (param) => {
    try {
        const conn = poolObj.promise();

        let sql = `DELETE FROM share_rule WHERE id=${param.id}`;
        let data = await conn.query(sql);
        console.log(data, '7777777777777777777777777777777777');
        return;
    }
    catch (e) {
        console.log(e);
    }

}

/**
 * 修改数据
 * @param {object} param.data
 * @param {string} param.id 规则id
 */
exports.updateOne = async (param) => {
    try {
        const conn = poolObj.promise();
        let str = '';
        for (let key in params.data) {
            str = str + `${key}='${params.data[key]}',`
        }
        str = str.substring(0, str.lastIndexOf(','));
        let sql = `UPDATE share_rule SET ${str} WHERE id=${param.id}`;
        let data = await conn.query(sql);
        console.log(data, '7777777777777777777777777777777777');
        return data;
    }
    catch (e) {
        console.log(e);
    }

}

/**
 * 查询当前时间生效的规则信息 
 */
exports.findRuleCurrentAndStartAndEnd = async () => {
    try {
        const conn = poolObj.promise();
        let sql = `SELECT id,state,share_number FROM share_rule  WHERE  start_date <= now() and end_date >= now()`;
        let data = await conn.query(sql);
        return data[0][0];
    }
    catch (e) {
        console.log(e);
    }

}